const models = require('../db/mysqldb/index')
const { Op } = require("sequelize");

class DataSourceModel {
  /**
   * @description 根据name查找,不包含删除数据
   * @param {string} name 
   * @returns 
   */
  static async findByName (name) {
    return await models.data_source.findOne({
      where: {
        name,
        delete_timestamp: {
          [Op.is]: null,
        }
      }
    })
  }
  /**
   * @description 通过path和id查找数据，不包含删除数据
   * @param {string} name 
   * @param {string} data_source_id 
   * @returns 
   */
  static async findByNameAndId (name, data_source_id,) {
    return await models.data_source.findOne({
      where: {
        name,
        delete_timestamp: {
          [Op.is]: null,
        },
        data_source_id: {
          [Op.ne]: data_source_id,
        }
      }
    })
  }
  /**
   * @description create
   * @param {*} name 
   * @param {*} db_type 
   * @param {*} host 
   * @param {*} port 
   * @param {*} db_name 
   * @param {*} user 
   * @param {*} password 
   * @param {*} remark 
   * @param {*} op_user_id 
   * @returns 
   */
  static async create (name, db_type, host, port, db_name, user, password, remark, op_user_id,) {
    return await models.data_source.create({
      name,
      db_type,
      host,
      port,
      db_name,
      user,
      password,
      remark,
      create_by: op_user_id
    })
  }
  /**
   * @description update
   * @param {*} name 
   * @param {*} db_type 
   * @param {*} host 
   * @param {*} port 
   * @param {*} db_name 
   * @param {*} user 
   * @param {*} password 
   * @param {*} remark 
   * @param {*} op_user_id 
   * @param {*} data_source_id 
   * @returns 
   */
  static async update (name, db_type, host, port, db_name, user, password, remark, op_user_id, data_source_id) {
    return await models.data_source.update(
      {
        name,
        db_type,
        host,
        port,
        db_name,
        user,
        password,
        remark,
        update_by: op_user_id,
        update_timestamp: Date.now()
      },
      {
        where: { data_source_id },
      }
    )
  }
  /**
   * @description delete
   * @param {*} op_user_id 
   * @param {*} data_source_id 
   * @returns 
   */
  static async delete (op_user_id, data_source_id) {
    return await models.data_source.update(
      {
        delete_timestamp: Date.now(),
        delete_by: op_user_id
      },
      {
        where: { data_source_id },
      }
    )
  }
  /**
   * @description list
   * @param {*} limit 
   * @param {*} size 
   * @param {*} name 
   * @param {*} db_type 
   * @param {*} host 
   * @param {*} db_name 
   * @param {*} remark 
   * @returns 
   */
  static async list (limit, size = 1, name, db_type, host, db_name, remark,) {
    let sql = `select SQL_CALC_FOUND_ROWS data_source_id,name,db_type,host,port,db_name,user,password,remark,update_by,update_timestamp,create_by,
create_timestamp from data_source where 1 = 1 AND delete_timestamp IS NULL `
    if (name) {
      sql = sql + `AND name like '%${name}%' `
    }
    if (db_type) {
      sql = sql + `AND db_type = '${db_type}' `
    }
    if (host) {
      sql = sql + `AND host = '${host}' `
    }
    if (db_name) {
      sql = sql + `AND db_name like '%${db_name}%' `
    }
    if (remark) {
      sql = sql + `AND remark like '%${remark}%' `
    }
    sql = sql + "ORDER BY create_timestamp DESC ,update_timestamp DESC "
    if (limit) {
      sql = sql + `LIMIT ${limit} OFFSET  ${(size - 1) * limit} `
    }
    const result = await models.sequelize.query(sql, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    const count = await models.sequelize.query(`SELECT FOUND_ROWS() as count;`, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    return { result, count }
  }
  /**
  * @description allList
  * @returns 
  */
  static async allList () {
    let sql = `select data_source_id as value,name as label from data_source where 1 = 1 AND delete_timestamp IS NULL ORDER BY create_timestamp DESC ,update_timestamp DESC `
    const result = await models.sequelize.query(sql, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    return result
  }
}
module.exports = {
  DataSourceModel,
}
